import pandas as pd
import logging
import os

# 配置日志
logging.basicConfig(
    filename='license_plate_analysis.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    encoding='utf-8'
)

# Excel文件路径
excel_path = r'c:\Users\HP\OneDrive\Desktop\所有文件\123\project黄飞燕\练习一\e\6月份各车牌号的总货运量表.xlsx'

# 验证文件是否存在
if not os.path.exists(excel_path):
    error_msg = f"Excel文件不存在: {excel_path}"
    logging.error(error_msg)
    raise FileNotFoundError(error_msg)

# 读取Excel数据
try:
    df = pd.read_excel(excel_path)
    logging.info(f"成功读取Excel文件，共{len(df)}条数据")
    print("Excel文件中的列名:", df.columns.tolist())  # 打印列名用于调试
    
    # 定义所需列名（根据文件实际情况可能需要调整）
    required_columns = ['车辆', '6月份总货运量']
    
    # 检查必要的列是否存在
    missing_columns = [col for col in required_columns if col not in df.columns]
    if missing_columns:
        error_msg = f"Excel文件缺少必要的列: {missing_columns}"
        logging.error(error_msg)
        raise ValueError(error_msg)
except Exception as e:
    logging.error(f"读取Excel文件失败: {e}")
    raise

# 按货运量降序排序
df_sorted = df.sort_values(by='6月份总货运量', ascending=False)

# 生成Markdown表格
markdown_table = "| 车辆 | 6月份总货运量 |\n|--------|-------------------|\n"
for _, row in df_sorted.iterrows():
    markdown_table += f"| {row['车辆']} | {row['6月份总货运量']:.2f} |\n"

# 保存Markdown表格到文件
output_path = 'license_plate_table.md'
with open(output_path, 'w', encoding='utf-8') as f:
    f.write(markdown_table)

logging.info(f"车牌号货运量表格已成功生成: {output_path}")
print(f"表格生成成功，请查看文件: {output_path}")